package com.gopay.scheduler.service.autosettle.impl;

import com.gopay.common.cipher.utils.SensitiveInfoUtils;
import com.gopay.common.domain.cps.CpsGenMainOrder;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.common.util.DateUtils;
import com.gopay.cron.web.vo.PayOrderVo;
import com.gopay.scheduler.service.autosettle.AutoSettleService;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service(value = "autoSettleService")
public class AutoSettleServiceImpl implements AutoSettleService {

	@Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;
	/**
	 * 查询订单之零点判断
	 * @param startDay
	 * @param today
	 * @return
	 */
	public List<CpsGenMainOrder> queryOrderOfJudge(String startDay,String today){
		List<CpsGenMainOrder> orderList=new ArrayList<CpsGenMainOrder>();
        
        StringBuilder sb = new StringBuilder();
      
        sb.append("  select m.gopay_order_id, ");
        sb.append("  m.gopay_int_txn_cd, ");
        sb.append("  p.CUST_AUDIT_TIME, ");
        sb.append("  m.pay_cust_id, ");
        sb.append("  m.pay_acct, ");
        sb.append("  p.settlement_cycle, ");
        sb.append("  p.recv_bank_acct_num, ");
        sb.append("  p.RECV_BANK_NAME, ");
        sb.append("  m.MER_ID, ");
        sb.append("  p.BC_FLAG, ");
        sb.append("  m.MER_TXN_AMT, ");
        sb.append("  p.SPLIT_FLAG ");
        sb.append("  from CPS_GEN_MAIN_ORDER m ");
        sb.append("  left join CPS_GEN_PAY_ORDER p on p.gopay_order_id=m.gopay_order_id ");
        sb.append("  left join wf_appr_task_dtl td on (td.target_id=m.gopay_order_id and td.approval_id=3) ");
        sb.append("  left join wf_appr_decision deci on deci.task_id=td.task_id ");
        sb.append("  where 1=1 ");
        sb.append("  and deci.decision_sq is null ");
        sb.append("  and p.auto_settle is null ");
        sb.append("  and p.settlement_cycle>0 ");
//        sb.append("  and (p.is_optimizing_settle != 1 or p.is_optimizing_settle is null )");//排除T+1优化订单
        sb.append("  and (p.SETTLEMENT_STATE is null or p.SETTLEMENT_STATE = 0 or (p.SETTLEMENT_TYPE = '01' and p.SETTLEMENT_STATE = 2)) ");//排除D1优化订单
        sb.append("  and m.ACCOUNTING_POINT='1' ");
        sb.append("  and m.txn_sta_cd=30000 ");
        sb.append("  and m.gopay_int_txn_cd in('00700','00701','00800','01301') "); //add 00701 退款转账 by zhangyu
        //sb.append("  and m.gopay_int_txn_cd in('00700','00800','01301','01605','01606') ");
        sb.append("  and p.CUST_AUDIT_TIME<to_timestamp(:dateEnd, 'yyyymmddHH24miss') ");
        sb.append("  and p.CUST_AUDIT_TIME>=to_timestamp(:dateBegin, 'yyyymmddHH24miss') ");
        //sb.append(" order by m.gopay_txn_tm asc ");
        
        sb.append(" union ");
        sb.append("  select m.gopay_order_id, ");
        sb.append("  m.gopay_int_txn_cd, ");
        sb.append("  p.CUST_AUDIT_TIME, ");
        sb.append("  m.pay_cust_id, ");
        sb.append("  m.pay_acct, ");
        sb.append("  p.settlement_cycle, ");
        sb.append("  p.recv_bank_acct_num, ");
        sb.append("  p.RECV_BANK_NAME, ");
        sb.append("  m.MER_ID, ");
        sb.append("  p.BC_FLAG, ");
        sb.append("  m.MER_TXN_AMT, ");
        sb.append("  p.SPLIT_FLAG ");
        sb.append("  from CPS_GEN_MAIN_ORDER m ");
        sb.append("  left join CPS_GEN_PAY_ORDER p on p.gopay_order_id=m.gopay_order_id ");
        sb.append("  left join wf_appr_task_dtl td on (td.target_id=m.gopay_order_id and td.approval_id=3) ");
        sb.append("  left join wf_appr_decision deci on deci.task_id=td.task_id ");
        sb.append("  where 1=1 ");
        sb.append("  and deci.decision_sq is null ");
        sb.append("  and p.auto_settle is null ");
        sb.append("  and p.settlement_cycle>0 ");
//        sb.append("  and (p.is_optimizing_settle != 1 or p.is_optimizing_settle is null )");//排除T+1优化订单
        sb.append("  and (p.SETTLEMENT_STATE is null or p.SETTLEMENT_STATE = 0 or (p.SETTLEMENT_TYPE = '01' and p.SETTLEMENT_STATE = 2)) ");//排除D1优化订单
        sb.append("  and m.ACCOUNTING_POINT='1' ");
        //状态为30402 已付款    				手机转账到银行卡，手机信用卡还款
        sb.append("  and m.txn_sta_cd=30402 ");
        sb.append("  and m.gopay_int_txn_cd in('01605','01606') ");
        //sb.append("  and m.gopay_int_txn_cd in('00700','00800','01301','01605','01606') ");
        sb.append("  and p.CUST_AUDIT_TIME<=to_timestamp(:dateEnd, 'yyyymmddHH24miss') ");
        sb.append("  and p.CUST_AUDIT_TIME>=to_timestamp(:dateBegin, 'yyyymmddHH24miss') ");
        
               
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("dateBegin",startDay+"000000");
       map.put("dateEnd",today+"000000");
        
        
        List<Object[]> rsList = commonQueryDAO.findBySql(sb.toString(), map);
        CpsGenMainOrder main;
        
        if (rsList != null && rsList.size() > 0) {
            for (Object[] arr : rsList) {
                main=new CpsGenMainOrder();
                main.setGopayOrderId((String) arr[0]);
                main.setGopayIntTxnCd((String) arr[1]);
                main.setGopayTxnTm((Date) arr[2]);
                main.setPayCustId((String) arr[3]);
                main.setPayAcct((String) arr[4]);
                
                int settleCircle=arr[5]!=null?((BigDecimal)arr[5]).intValue():0;

                //modify by tianxin 2018年1月18日
                //将payorder里的卡号解密放入list，以便后续查找联行号
                String cardNum= SensitiveInfoUtils.smartDecrypt((String) arr[6]);

                String bankName=(String) arr[7];
                
                main.setMerId((String) arr[8]);
                
                main.setTransDtlSq(settleCircle);
                main.setRemark1(cardNum);
                main.setRemark2(bankName);
                main.setOrderType(((BigDecimal) arr[9]).toString());
                logger.error("====>"+((BigDecimal) arr[9]).toString());
                main.setMerTxnAmt((BigDecimal)arr[10]);
                main.setChildTranType((String)arr[11]);
                
                orderList.add(main);
            }
        }
        
        return orderList;
	}
	protected Logger logger = Logger.getLogger(getClass());
	
	
	/**
	 * 查询订单之十点银企直连
	 * @param startDay
	 * @param today
	 * @return
	 */
	public List<CpsGenMainOrder> queryOrderOfBCDL(String startDay,String today){
		List<CpsGenMainOrder> orderList=new ArrayList<CpsGenMainOrder>();
        
        StringBuilder sb = new StringBuilder();
      
        sb.append("  select m.gopay_order_id, ");
        sb.append("  m.gopay_int_txn_cd ");
        sb.append("  from CPS_GEN_MAIN_ORDER m ");
        sb.append("  left join CPS_GEN_PAY_ORDER p on p.gopay_order_id=m.gopay_order_id ");
        sb.append("  left join wf_appr_task_dtl td on (td.target_id=m.gopay_order_id and td.approval_id=3) ");
        sb.append("  left join wf_appr_decision deci on deci.task_id=td.task_id ");
        sb.append("  where 1=1 ");
        sb.append("  and deci.decision_sq is null ");
        sb.append("  and p.rule_id is not null ");
        sb.append("  and p.PAY_STAT ='01' ");
        sb.append("  and p.auto_settle ='1' ");
        sb.append("  and p.settlement_cycle>0 ");
//        sb.append("  and (p.is_optimizing_settle != 1 or p.is_optimizing_settle is null )");//排除T+1优化订单
        sb.append("  and (p.SETTLEMENT_STATE is null or p.SETTLEMENT_STATE = 0 or (p.SETTLEMENT_TYPE = '01' and p.SETTLEMENT_STATE = 2)) ");//排除D1优化订单
        sb.append("  and m.ACCOUNTING_POINT='1' ");
        sb.append("  and m.txn_sta_cd=30000 ");
        sb.append("  and m.gopay_int_txn_cd in('00700','00701','00800','01301') ");
//        sb.append("  and m.gopay_int_txn_cd in('00700','00800','01301','01605','01606') ");
        sb.append("  and p.CUST_AUDIT_TIME<to_timestamp(:dateEnd, 'yyyymmddHH24miss') ");
        sb.append("  and p.CUST_AUDIT_TIME>=to_timestamp(:dateBegin, 'yyyymmddHH24miss') ");
        //sb.append(" order by m.gopay_txn_tm asc ");
        
        sb.append("  union ");
        sb.append("  select m.gopay_order_id, ");
        sb.append("  m.gopay_int_txn_cd ");
        sb.append("  from CPS_GEN_MAIN_ORDER m ");
        sb.append("  left join CPS_GEN_PAY_ORDER p on p.gopay_order_id=m.gopay_order_id ");
        sb.append("  left join wf_appr_task_dtl td on (td.target_id=m.gopay_order_id and td.approval_id=3) ");
        sb.append("  left join wf_appr_decision deci on deci.task_id=td.task_id ");
        sb.append("  where 1=1 ");
        sb.append("  and deci.decision_sq is null ");
        sb.append("  and p.rule_id is not null ");
        sb.append("  and p.PAY_STAT ='01' ");
        sb.append("  and p.auto_settle ='1' ");
        sb.append("  and p.settlement_cycle>0 ");
//        sb.append("  and (p.is_optimizing_settle != 1 or p.is_optimizing_settle is null )");//排除T+1优化订单
        sb.append("  and (p.SETTLEMENT_STATE is null or p.SETTLEMENT_STATE = 0 or (p.SETTLEMENT_TYPE = '01' and p.SETTLEMENT_STATE = 2)) ");//排除D1优化订单
        sb.append("  and m.ACCOUNTING_POINT='1' ");
        //状态为30402 已付款    				手机转账到银行卡，手机信用卡还款
        sb.append("  and m.txn_sta_cd=30402 ");
        sb.append("  and m.gopay_int_txn_cd in('01605','01606') ");
//        sb.append("  and m.gopay_int_txn_cd in('00700','00800','01301','01605','01606') ");
        sb.append("  and p.CUST_AUDIT_TIME<to_timestamp(:dateEnd, 'yyyymmddHH24miss') ");
        sb.append("  and p.CUST_AUDIT_TIME>=to_timestamp(:dateBegin, 'yyyymmddHH24miss') ");
        
               
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("dateBegin",startDay+"000000");
        map.put("dateEnd",today+"000000");
       
        
        
        List<Object[]> rsList = commonQueryDAO.findBySql(sb.toString(), map);
        CpsGenMainOrder main;
        
        if (rsList != null && rsList.size() > 0) {
            for (Object[] arr : rsList) {
                main=new CpsGenMainOrder();
                main.setGopayOrderId((String) arr[0]);
                main.setGopayIntTxnCd((String) arr[1]);
                
                orderList.add(main);
            }
        }
        
        return orderList;
	}


	@Override
	public List<PayOrderVo> queryOrderOfSettlementToday(String startDay, String today) {

		List<PayOrderVo> orderList=new ArrayList<PayOrderVo>();
        
        StringBuilder sb = new StringBuilder();
      
        sb.append("  select m.gopay_order_id, ");
        sb.append("  p.RECV_BANK_NAME, ");
        sb.append("  m.MER_TXN_AMT, ");
        sb.append("  m.OUT_STLM_ID ");
        sb.append("  from CPS_GEN_MAIN_ORDER m ");
        sb.append("  left join CPS_GEN_PAY_ORDER p on p.gopay_order_id=m.gopay_order_id ");
        sb.append("  left join wf_appr_task_dtl td on (td.target_id=m.gopay_order_id and td.approval_id=3) ");
        sb.append("  left join wf_appr_decision deci on deci.task_id=td.task_id ");
        sb.append("  where 1=1 ");
        sb.append("  and deci.decision_sq is null ");
        sb.append("  and (p.PAY_STAT is null or p.PAY_STAT='01') ");
        sb.append("  and p.auto_settle = 1 ");
        sb.append("  and p.settlement_cycle>0 ");
        sb.append("  and p.IS_OPTIMIZING_SETTLE = 1 ");
        sb.append("  and m.ACCOUNTING_POINT='1' ");
        sb.append("  and m.txn_sta_cd=30000 ");
        sb.append("  and m.gopay_int_txn_cd in('00700','00701','00800') ");
        sb.append("  and p.CUST_AUDIT_TIME<=to_timestamp(:dateEnd, 'yyyymmddHH24miss')+1 ");
        sb.append("  and p.CUST_AUDIT_TIME>=to_timestamp(:dateBegin, 'yyyymmddHH24miss') ");
               
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("dateBegin",startDay+"000000");
        map.put("dateEnd",today+"000000");
       
        
        
        List<Object[]> rsList = commonQueryDAO.findBySql(sb.toString(), map);
        
        if (rsList != null && rsList.size() > 0) {
            for (Object []s : rsList) {
            	PayOrderVo p = new PayOrderVo();
            	
            	p.setGopayOrderId((String) s[0]);
            	p.setRecvBankName((String) s[1]);
            	p.setMerTxnAmt((BigDecimal) s[2]);
                p.setOutStlmId((String) s[3]);
            	orderList.add(p);
            }
        }
        
        return orderList;
	
	}
	
	@Override
	public List<CpsGenMainOrder> queryOrderByTypeForDailySettlementJudge(String type) {
		// TODO Auto-generated method stub
		List<CpsGenMainOrder> orderList=new ArrayList<CpsGenMainOrder>();        
        StringBuilder sb = new StringBuilder();
      
        sb.append("  select m.gopay_order_id, ");
        sb.append("  p.RECV_BANK_NAME, ");
        sb.append("  m.MER_TXN_AMT, ");
        sb.append("  m.OUT_STLM_ID ");
        sb.append("  from CPS_GEN_MAIN_ORDER m ");
        sb.append("  left join CPS_GEN_PAY_ORDER p on p.gopay_order_id=m.gopay_order_id ");
        sb.append("  left join wf_appr_task_dtl td on (td.target_id=m.gopay_order_id and td.approval_id=3) ");
        sb.append("  left join wf_appr_decision deci on deci.task_id=td.task_id ");
        sb.append("  where 1=1 ");
        sb.append("  and deci.decision_sq is null ");
        sb.append("  and (p.PAY_STAT is null or p.PAY_STAT='01') ");
        sb.append("  and p.auto_settle = 1 ");
        sb.append("  and p.settlement_cycle = 1 ");
        sb.append("  and p.SETTLEMENT_TYPE = '01' ");
        sb.append("  and p.SETTLEMENT_STATE = 1 ");
        sb.append("  and p.REAL_SETTLEMENT_OPERATION_TYPE = '"+type+"' ");
        sb.append("  and m.ACCOUNTING_POINT='1' ");
        sb.append("  and m.txn_sta_cd=30000 ");
        sb.append("  and m.gopay_int_txn_cd in('00700','00800') ");
        sb.append("  and p.CUST_AUDIT_TIME<=to_timestamp(:dateEnd, 'yyyymmddHH24miss') ");
        sb.append("  and p.CUST_AUDIT_TIME>=to_timestamp(:dateBegin, 'yyyymmddHH24miss') ");
               
        Map<String, Object> map = new HashMap<String, Object>();
        String curDate = DateUtils.format(new Date(), DateUtils.FILEDATE_PATTERM);
        map.put("dateBegin",curDate+"000000");
        map.put("dateEnd",curDate+"090000");
       
        
        
        List<Object[]> rsList = commonQueryDAO.findBySql(sb.toString(), map);
        CpsGenMainOrder main;
        
        if (rsList != null && rsList.size() > 0) {
            for (Object[] arr : rsList) {
                main=new CpsGenMainOrder();
                main.setGopayOrderId((String) arr[0]);
                main.setGopayIntTxnCd((String) arr[1]);
                
                orderList.add(main);
            }
        }
        
        return orderList;
	}


//	@Override
//	public Map<String, BigDecimal> getSumGroupByPayBankCode(String startDay, String today) {
//
//        StringBuilder sb = new StringBuilder();
//
//        sb.append("  select nvl(sum(mer_txn_amt),0),p.recv_bank_name ");
//        sb.append("  from CPS_GEN_MAIN_ORDER m ");
//        sb.append("  left join CPS_GEN_PAY_ORDER p on p.gopay_order_id=m.gopay_order_id ");
//        sb.append("  left join wf_appr_task_dtl td on (td.target_id=m.gopay_order_id and td.approval_id=3) ");
//        sb.append("  left join wf_appr_decision deci on deci.task_id=td.task_id ");
//        sb.append("  where 1=1 ");
//        sb.append("  and deci.decision_sq is null ");
//        sb.append("  and p.auto_settle = 1 ");
//        sb.append("  and p.settlement_cycle>0 ");
//        sb.append("  and p.IS_OPTIMIZING_SETTLE = 1 ");
//        sb.append("  and m.ACCOUNTING_POINT='1' ");
//        sb.append("  and m.txn_sta_cd=30000 ");
//        sb.append("  and m.gopay_int_txn_cd in('00700','00800') ");
//        sb.append("  and p.CUST_AUDIT_TIME<=to_timestamp(:dateEnd, 'yyyymmddHH24miss')+1 ");
//        sb.append("  and p.CUST_AUDIT_TIME>=to_timestamp(:dateBegin, 'yyyymmddHH24miss') ");
//        sb.append("  group by p.recv_bank_name ");
//
//        Map<String, Object> map = new HashMap<String, Object>();
//        map.put("dateBegin",startDay+"000000");
//        map.put("dateEnd",today+"000000");
//
//
//
//        List<Object[]> rsList = commonQueryDAO.findBySql(sb.toString(), map);
//
//        Map<String,BigDecimal> m = new HashMap<String,BigDecimal>();
//        if (rsList != null && rsList.size() > 0) {
//            for (Object[] s : rsList) {
//            	m.put(s[1].toString(), (BigDecimal)s[0]);
//            }
//        }
//
//        return m;
//
//	}
}
